package cn.com.ry.framework.mongodb.adatper.util;

import com.alibaba.fastjson.JSON;
import com.mongodb.DBObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * mongodb转换成mysql语法
 */
public class MongoDBToMysqlTool {
// $set $unset $push $pull $pop
// $mod $inc
// $exists $type $size 实现
// distinct() group() count() $elemMatch $sample $geometry
// $not $geoWithin  $near $nearSphere $geoIntersects $maxDistance $minDistance
// $and $or $nor 实现
// $in $nin $all $regex $lg $ne $lge $gt $gte  实现
    public static String COLUMN_NAME = "datajson";


    /**
     * mongodb 语法 转 mysql语法
     *
     * @param table
     * @param query
     * @return
     */
    public static String mongoDBToMysqlForFind(String table, String colunmName, Query query) {
        StringBuffer sb = new StringBuffer();
        sb.append("select " + COLUMN_NAME + " from " + table + "  ");
        MongoDBToMysqlTool.COLUMN_NAME = colunmName;
        String whereSql = "";
        if (query != null) {
            whereSql = whereSql(table, colunmName, query);
        }
        return sb.append(whereSql).toString();
    }

    /**
     * 获取总数
     *
     * @param table
     * @param colunmName
     * @param query
     * @return
     */
    public static String mongoDBToMysqlForFindCount(String table, String colunmName, Query query) {
        StringBuffer sb = new StringBuffer();
        sb.append("select count(*) as count from " + table + " ");
        MongoDBToMysqlTool.COLUMN_NAME = colunmName;
        String whereSql = "";
        if (query != null) {
            whereSql = whereSql(table, colunmName, query);
        }
        return sb.append(whereSql).toString();
    }


    /**
     * 根据条件删除
     *
     * @param table
     * @param colunmName
     * @param query
     * @return
     */
    public static String mongoDBToMysqlForDel(String table, String colunmName, Query query) {
        StringBuffer sb = new StringBuffer();
        sb.append("delete  from " + table + " ");
        MongoDBToMysqlTool.COLUMN_NAME = colunmName;
        String whereSql = "";
        if (query != null) {
            whereSql = whereSql(table, colunmName, query);
        }
        return sb.append(whereSql).toString();
    }

    public static String mongoDBToMysqlForAutoIncreaseID(String idName, String table) {
        return "update " + table + " set " + COLUMN_NAME + "=JSON_SET(" + COLUMN_NAME + ",'$." + idName + "',CAST(" + toMysqlCol(table, idName) + "+1 AS SIGNED)) where " + toMysqlCol(table, "name") + "=" + idName;
    }

    /**
     * 只能更新一层
     *
     * @param table 表名
     * @param
     * @return
     */
    public static String mongoDBToMysqlForUpdate(String table, Query query, Update update) {
        StringBuffer sb = new StringBuffer();
        if (update != null && update.getUpdateObject() != null) {
            sb.append("update " + table + " set " + COLUMN_NAME + "=");
            //TODO 修改bug
            String updateJson = getUpdateSql(update);
            query.limit(0);
            query.skip(0);
            String whereSql = whereSql(table, MongoDBToMysqlTool.COLUMN_NAME, query);
            if (whereSql != null && !whereSql.trim().equals("")) {
                sb.append(updateJson + " " + whereSql);
            } else {
                sb.append(updateJson);
            }
        }
        return sb.toString();
    }

    /**
     * 通过ID更新
     *
     * @param table
     * @param id
     * @param update
     * @return
     */
    public static String mongoDBToMysqlForUpdateById(String table, String id, Update update) {
        StringBuffer sb = new StringBuffer();
        if (update != null) {
            sb.append("update " + table + " set " + COLUMN_NAME + "=");
            String updateJson = getUpdateSql(update);
            sb.append(updateJson + " where _id='" + id + "'");
        }
        return sb.toString();
    }

    /**
     * 获取wheresql
     *
     * @param colunmName
     * @param query
     * @return
     */
    public static String whereSql(String table, String colunmName, Query query) {
        DBObject queryDBObject = query.getQueryObject();
        Map queryMap = new HashMap();
        if (queryDBObject != null) {
            queryMap = queryDBObject.toMap();
        }
        int limit = query.getLimit();
        int skip = query.getSkip();
        DBObject sortDBObject = query.getSortObject();
        Map sortMap = new HashMap();
        if (sortDBObject != null) {
            sortMap = sortDBObject.toMap();
        }
        return whereSql(table, colunmName, queryMap, sortMap, skip, limit);
    }


    public static String whereSql(String table, String colunmName, String query) {
        Map map = JSON.parseObject(query, Map.class);
        return whereSql(table, colunmName, map, null, 0, 0);
    }

    /**
     * 获取条件
     *
     * @param table      表名
     * @param colunmName 字段名称
     * @param queryMap   查询条件
     * @param sortMap    排序字段
     * @param skip       跳过条数
     * @param limit      显示条数
     * @return
     */
    public static String whereSql(String table, String colunmName, Map queryMap, Map sortMap, int skip, int limit) {
        StringBuffer sb = new StringBuffer();

        MongoDBToMysqlTool.COLUMN_NAME = colunmName;

        try {
            if (queryMap != null && !queryMap.isEmpty()) {
                Set keySet = queryMap.keySet();
                Iterator it = keySet.iterator();
                //优化sql,提升性能，数据格式为 id,datajson
                for (; it.hasNext(); ) {
                    String key = (String) it.next();
                    Object value = queryMap.get(key);
                    if (key.equals("_id")) {
                        sb.append(" and _id='" + value + "'");
                    } else {
                        if (value instanceof List) {
                            opForLogic(table, key, (List<Map>) value, sb);
                        } else if (value instanceof Map) {
                            opForCal(table, "and", key, (Map) value, sb);
                        } else {
                            opForValue(table, "and", key, value, sb);
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        // order by
        if (sortMap != null && !sortMap.isEmpty()) {
            sb.append(" order by ");
            Iterator it = sortMap.keySet().iterator();
            int sortIndex = 0;
            for (; it.hasNext(); ) {
                sortIndex++;
                if (sortIndex > 1) {
                    sb.append(",");
                }
                String sortKey = (String) it.next();
                int sortValue = (int) sortMap.get(sortKey);
                if (sortValue == 1) {
                    sb.append(toMysqlCol(table, sortKey) + " ASC");
                } else if (sortValue == -1) {
                    sb.append(toMysqlCol(table, sortKey) + " DESC");
                }
            }
        }
        //limit
        if (!(limit == 0 && skip == 0)) {
            sb.append(" limit " + skip + "," + limit);
        }
        String resultSql = sb.toString().trim();
        if (resultSql.startsWith("and")) {
            resultSql = resultSql.substring(3);
        }

        if (!resultSql.trim().equals("") && !resultSql.trim().startsWith("order by") && !resultSql.trim().startsWith("limit")) {
            resultSql = " where " + resultSql;
        }
        return resultSql;
    }

    /**
     * 字段变mysql语法
     *
     * @param key
     * @return
     */
//    public static String toMysqlCol(String key) {
//        return "JSON_EXTRACT(" + COLUMN_NAME + ",'$." + key + "')";
//    }
    public static String toMysqlCol(String tableName, String key) {
        String col_sql = "JSON_EXTRACT(" + COLUMN_NAME + ",'$." + key + "')";
        if (!StringUtils.isBlank(tableName)) {
            if (tableName.equalsIgnoreCase("mqmes")
                    && (key.equalsIgnoreCase("date") || key.equalsIgnoreCase("mesId"))) {
                col_sql = "`" + key + "`";
            } else if (tableName.equalsIgnoreCase("serialnum") && key.equalsIgnoreCase("key")) {
                col_sql = "`" + key + "`";
            } else if (tableName.equalsIgnoreCase("timerloginfo")
                    && (key.equalsIgnoreCase("endTime") || key.equalsIgnoreCase("timerCode"))) {
                col_sql = "`" + key + "`";
            }
        }
        return col_sql;
    }

    /**
     * like处理
     *
     * @param value
     * @return
     */
    public static String opLike(String value) {
        String result = "";

        // .* .*    /^ ^/    两种，，，模式
        if (value.indexOf(".*") > -1) {
            return "'" + value.replace(".*", "%") + "'";
        }

        if (!value.startsWith("/")) {
            value = "/" + value;
        }
        if (!value.endsWith("/")) {
            value = value + "/";
        }
        if (value != null && value.trim().length() > 2) {
            char[] chars = value.toCharArray();
            if (chars[0] == '/' && chars[1] != '^' && chars[chars.length - 2] != '^' && chars[chars.length - 1] == '/') {
                value = value.substring(1, value.length() - 1);
                result = "%" + value + "%";
            } else if (chars[0] == '/' && chars[1] != '^' && chars[chars.length - 2] == '^' && chars[chars.length - 1] == '/') {
                value = value.substring(1, value.length() - 2);
                result = "%" + value;
            } else if (chars[0] == '/' && chars[1] == '^' && chars[chars.length - 2] != '^' && chars[chars.length - 1] == '/') {
                value = value.substring(2, value.length() - 1);
                result = value + "%";
            } else if (chars[0] == '/' && chars[1] == '^' && chars[chars.length - 2] == '^' && chars[chars.length - 1] == '/') {
                value = value.substring(2, value.length() - 2);
                result = value;
            }
        }
        return "'" + result + "'";
    }

    /**
     * type o:[{k:v},{k:v}]
     * $or $and $nor
     *
     * @param op    操作符
     * @param value
     * @param sb
     */
    public static void opForLogic(String table, String op, List<Map> value, StringBuffer sb) {
        String flag = "";
        if (op.equals("$or")) {
            flag = "or";
        } else if (op.equals("$and")) {
            flag = "and";
        } else if (op.equals("$nor")) {
            flag = "or !";
        }
        if (value != null && value.size() > 0) {
            for (int i = 0; i < value.size(); i++) {
                //开始解析内层操作
                Map map = value.get(i);
                if (map.keySet().size() > 0) {
                    String subKey = (String) map.keySet().iterator().next();
                    Object subValue = map.get(subKey);
                    if (subValue instanceof List) {
                        opForLogic(table, subKey, (List<Map>) subValue, sb);
                    } else if (subValue instanceof Map) {
                        sb.append(" " + flag + " (");
                        opForCal(table, flag, subKey, (Map) subValue, sb);
                        sb.append(")");
                    } else {
                        opForValue(table, flag, subKey, subValue, sb);
                    }
                }
            }
        }
    }

    /**
     * 处理算数运算
     * k:{o:v,o:v}
     *
     * @param key
     */
    public static void opForCal(String table, String flag, String key, Map value, StringBuffer sb) {
        if (value != null && !value.isEmpty()) {
            Set keySet = value.keySet();
            Iterator it = keySet.iterator();
            for (int k = 0; it.hasNext(); k++) {
                if (!sb.toString().trim().endsWith("(")) {
                    sb.append(" " + flag + " ");
                }
                String calKey = (String) it.next();
                if (calKey.equals("$in") || calKey.equals("$nin")) {
                    if (key.equals("$in")) {
                        sb.append(toMysqlCol(table, key) + " in (");
                    } else {
                        sb.append(toMysqlCol(table, key) + " not in (");
                    }
                    List calValue = (List) value.get(calKey);

                    if (calValue != null && calValue.size() > 0) {
                        for (int i = 0; i < calValue.size(); i++) {
                            if (i == calValue.size() - 1) {
                                if (calValue.get(i) instanceof String) {
                                    sb.append("'" + calValue.get(i) + "')");
                                } else {
                                    sb.append(calValue.get(i) + ")");
                                }
                            } else {
                                if (calValue.get(i) instanceof String) {
                                    sb.append("'" + calValue.get(i) + "',");
                                } else {
                                    sb.append(calValue.get(i) + ",");
                                }
                            }
                        }
                    }
                } else if (calKey.equals("$regex")) {
                    //like
                    sb.append(toMysqlCol(table, key) + " like " + opLike((String) value.get(calKey)));
                } else if (calKey.equals("$size")) {
                    //like
                    sb.append("JSON_LENGTH(" + toMysqlCol(table, key) + ")  =" + value.get(calKey));
                } else if (calKey.equals("$exists")) {
                    //exists
                    sb.append("JSON_CONTAINS_PATH(" + COLUMN_NAME + ",'one', '$." + key + "')=" + value.get(calKey));

                } else if (calKey.equals("$date")) {
                    //date
                    try {
                        String dateValue = UTCToCST((String) value.get(calKey), "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
                        sb.append(toMysqlCol(table, key) + "='" + dateValue + "'");
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }

                } else if (calKey.equals("$type")) {
                    //type
                    //mongodb: 	1	“double”	2	“string” 	3	“object” 	4	“array”	6	“undefined” 	8	“bool” 16	“int” 18	“long” 	10	“null”
                    //mysql:ARRAY  STRING  INTEGER  DOUBLE  OBJECT  BOOLEAN  "UNSIGNED INTEGER"  NULL
                    int calType = (int) value.get(calKey);
                    String mysqlType = "";
                    String op = "=";
                    if (calType == 1 || calType == 18) {
                        mysqlType = "DOUBLE";
                    } else if (calType == 2) {
                        mysqlType = "STRING";
                    } else if (calType == 3) {
                        mysqlType = "OBJECT";
                    } else if (calType == 4) {
                        mysqlType = "ARRAY";
                    } else if (calType == 8) {
                        mysqlType = "BOOLEAN";
                    } else if (calType == 10) {
                        //is  null
                        mysqlType = "NULL";
                    } else if (calType == 16) {
                        mysqlType = "INTEGER";
                    } else if (calType == 6) {
                        op = " is ";
                        mysqlType = " NULL";
                    }
                    sb.append("JSON_TYPE(" + toMysqlCol(table, key) + ")" + op + mysqlType);

                } else if (calKey.equals("$all")) {
                    //all
                    List allList = (List) value.get(calKey);
                    if (allList != null && allList.size() > 0) {
                        String allStr = allList.toString();
                        allStr = allStr.substring(1, allStr.length() - 1);
                        sb.append("JSON_CONTAINS(" + toMysqlCol(table, key) + ", JSON_ARRAY(" + allStr + "))");
                    }
                } else if (calKey.equals("$elemMatch")) {
                    //$elemMatch
                    String allStr = "";
                    Map elemMatch = (Map) value.get(calKey);
                    if (elemMatch != null && !elemMatch.isEmpty()) {
                        Iterator elemMathIt = elemMatch.keySet().iterator();
                        for (; elemMathIt.hasNext(); ) {
                            String elemMathKey = (String) elemMathIt.next();
                            Object elemMathValue = elemMatch.get(elemMathKey);
                            if (elemMathValue instanceof String) {
                                allStr += "," + "'" + elemMathKey + "','" + elemMathValue + "'";
                            } else if (elemMathValue instanceof Date) {
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
                                allStr += "," + "'" + elemMathKey + "','" + simpleDateFormat.format(elemMathValue) + "'";
                            } else if (elemMathValue instanceof Pattern) {
                                allStr += "," + "'" + elemMathKey + "'," + opLike(elemMathValue.toString());
                            } else {
                                allStr += "," + "'" + elemMathKey + "'," + elemMathValue;
                            }
                        }
                        allStr = allStr.substring(1);
                    }
                    sb.append("JSON_CONTAINS(" + toMysqlCol(table, key) + ", JSON_OBJECT(" + allStr + "))");
                } else {
                    String opSql = "";
                    if (calKey.equals("$ne")) {
                        opSql = " <> ";
                    } else if (calKey.equals("$gt")) {
                        opSql = " > ";
                    } else if (calKey.equals("$gte")) {
                        opSql = " >= ";
                    } else if (calKey.equals("$lt")) {
                        opSql = " < ";
                    } else if (calKey.equals("$lte")) {
                        opSql = " <= ";
                    }
                    if (value.get(calKey) instanceof String) {
                        sb.append(toMysqlCol(table, key) + opSql + "'" + value.get(calKey) + "'");
                    } else if (value.get(calKey) instanceof Date) {
                        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
                        sb.append(toMysqlCol(table, key) + opSql + "'" + simpleDateFormat.format(value.get(calKey)) + "'");
                    } else if (value.get(calKey) instanceof Pattern) {
                        sb.append(toMysqlCol(table, key) + opSql + opLike(value.get(calKey).toString()));
                    } else {
                        sb.append(" case when JSON_TYPE(" + toMysqlCol(table, key) + ")='INTEGER' then " + toMysqlCol(table, key) + opSql + value + " else " + toMysqlCol(table, key) + opSql + "'" + value + "' end");
                    }
                }
            }
        }

    }

    /**
     * 等于
     * k:v
     */

    public static void opForValue(String table, String flag, String key, Object value, StringBuffer sb) {
        if (!(value instanceof List || value instanceof Map)) {
            if (value instanceof String) {
                sb.append(" " + flag + " " + toMysqlCol(table, key) + "='" + value + "'");
            } else if (value instanceof Date) {
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
                sb.append(" " + flag + " " + toMysqlCol(table, key) + "='" + simpleDateFormat.format(value) + "'");
            } else if (value instanceof Pattern) {
                sb.append(" " + flag + " " + toMysqlCol(table, key) + " like " + opLike(value.toString()));
            } else {
                sb.append(" " + flag + " case when JSON_TYPE(" + toMysqlCol(table, key) + ")='INTEGER' then " + toMysqlCol(table, key) + "=" + value + " else " + toMysqlCol(table, key) + "='" + value + "' end");
            }
        }
    }


    /**
     * 标准时间变成北京时间
     *
     * @param UTCStr
     * @param format
     * @return
     * @throws ParseException
     */
    public static String UTCToCST(String UTCStr, String format) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        Date date = sdf.parse(UTCStr);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.set(Calendar.HOUR, calendar.get(Calendar.HOUR) + 8);
        return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(calendar.getTime());
    }


    public static String getUpdateSql(Update update) {
        String updateData = "";
        DBObject dbObject = update.getUpdateObject();
        Map updateMap = (Map) dbObject.get("$set");
        if (updateMap != null && !updateMap.isEmpty()) {
            Set keySet = updateMap.keySet();
            Iterator it = keySet.iterator();
            StringBuffer sb = new StringBuffer();
            sb.append(" JSON_SET(" + COLUMN_NAME);
            for (; it.hasNext(); ) {
                String key = (String) it.next();
                Object value = updateMap.get(key);
                sb.append(",'$." + key + "'");
                if (value instanceof String) {
                    sb.append(",'" + value + "'");
                } else if (value instanceof Date) {
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
                    sb.append(",'" + simpleDateFormat.format(value) + "'");
                } else {
                    sb.append("," + value);
                }
            }
            sb.append(")");
            updateData = sb.toString();
        } else {
            updateData = "'" + JSON.toJSONString(update.getUpdateObject(), ValueFilterTool.getValueFilter()) + "'";
        }

        return updateData;
    }
}
